clear all
set more off

global temp "C:\Users\wb381032\OneDrive - WBG\Research\brazil earmarks\RCFS\Submission 3\Replication files"
global results "C:\Users\wb381032\OneDrive - WBG\Research\brazil earmarks\RCFS\Submission 3\Replication files\Results"

cd "$temp"

// Replication Files for "Market Power and the Transmission of Loan Subsidies"

/*------------------------------------------------------------------------------
 TABLE 1: Summary Statistics
------------------------------------------------------------------------------*/

*Panel A. Loan level data: working capital loans of recipient firms
	clear
	use pseudo_data.dta

	*keep sample of firms that obtain at least one earmarked loan 
	keep if ever_earmark == 1

	eststo: estpost sum earmark spread lvolume collateral maturity BF_rel rating HH, detail
	esttab using "$results\table1.csv", cells("mean p50 sd count") plain label replace
	eststo clear

*Panels B and C. Firm-bank-year level data: recipient and non-recipient firms
	clear
	use pseudo_data_fby.dta

	bysort firm_id: egen ever_ear = max(earmark)
	gen pre_ear = earmark==0 
	sort firm_id bank_id year
	bysort firm_id bank_id : replace pre_ear = 1 if earmark==1 & earmark[_n-1]==0 
	bysort firm_id bank_id : replace pre_ear = 1 if earmark[1]==1

	eststo: estpost sum age size BF_rel rating HH Lrgbk Leqbk if pre_ear == 1 & ever_ear == 1, detail
	esttab using "$results\table1.csv", cells("mean p50 sd count") plain label append
	eststo clear

	eststo: estpost sum age size BF_rel rating HH Lrgbk Leqbk if ever_ear == 0, detail
	esttab using "$results\table1.csv", cells("mean p50 sd count") plain label append
	eststo clear

*Panel D. Firm-bank level data at the time of first earmarked loan 
	clear
	use pseudo_data_fb.dta
	
	eststo: estpost sum bundle age size BF_rel HH rating earmarked_rating, detail
	esttab using "$results\table1.csv", cells("mean p50 sd count") plain label append
	eststo clear
	
*Save all panels in one table	
	insheet using "$results\table1.csv", noname clear
	drop if _n == 11 | _n == 21  | _n == 31  | _n == 41 
	replace v1 = subinstr(v1, "(mean)", "", .)
	replace v1 = subinstr(v1, "(max)", "", .)
	replace v2 = "Mean" if _n == 2
	replace v3 = "Median" if _n == 2
	replace v4 = "Std Dev" if _n == 2
	replace v5 = "# Obs" if _n == 2
	
	*Panel A
	insobs 1, after(2)
	replace v2 = "" if _n == 1 
	replace v1 = "Table 1. Summary Statistics" if _n == 1 
	replace v1 = "Panel A. Loan level data: working capital loans of recipient firms" if _n == 3 

	*Panel B
	replace v2 = "" if _n == 12
	for var v*: replace X = "" if _n == 13 
	replace v1 = "Panel B. Firm-bank-year level data: recipient firms" if _n == 13

	*Panel C
	replace v2 = "" if _n == 21 
	for var v*: replace X = "" if _n == 22 
	replace v1 = "Panel C. Firm-bank-year level data: non-recipient firms" if _n == 22

	*Panel D
	replace v2 = "" if _n == 30 
	for var v*: replace X = "" if _n == 31 
	replace v1 = "Panel D. Firm-bank level data at the time of first earmarked loan" if _n == 31

	export excel using "$results\Tables.xlsx", sheet("1") sheetreplace
	erase "$results\table1.csv"


/*------------------------------------------------------------------------------
 TABLE 2: Effect of earmarked loans on interest rate spreads of 
 working capital loans
------------------------------------------------------------------------------*/

clear
use pseudo_data.dta

*keep sample of firms that obtain at least one earmarked loan 
keep if ever_earmark == 1

* Double and triple interactions
gen earmark_risk     = earmark * risk 
gen earmark_MP      = earmark * MP
gen earmark_risk_MP = earmark * risk * MP 
gen risk_MP         = risk * MP

*Variable to inlude Location*Sector*Month fixed effects
egen group_ilm = group(zipcode sector month)

local out      "se bdec(3) rdec(3) symbol(***,**,*)"
local control = "lerner MP BF_rel lvolume collateral maturity"
local FE      = "date rating loan_index firm_id#bank_id bank_id#date" 

* 1) Baseline reg with Location*Sector*Month FE
	reghdfe spread earmark `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table2.xls", replace `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 2) Baseline with Firm*Year FE
	reghdfe spread earmark `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table2.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark) /// 
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  

* 3) Reg by risk with Location*Sector*Month FE
	reghdfe spread earmark earmark_risk risk `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table2.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 4) Reg by risk with Firm*Year FE
	reghdfe spread earmark earmark_risk risk `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table2.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  
	
* 5) Reg by risk & MP with Location*Sector*Month FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table2.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	sortvar(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 6) Reg by risk & MP with Firm*Year FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table2.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon ///
	keep(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	sortvar(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  


insheet using "$results\table2.txt", noname clear
insobs 1, before(1)
replace v1 = "Table 2. Effect of earmarked loans on interest rate spreads of working capital loans" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("2") sheetreplace
erase "$results\table2.txt"
erase "$results\table2.xls"	
	
/*------------------------------------------------------------------------------
 TABLE 3: Effect of earmarked loans on interest rate spreads of 
 working capital loans (alternative definition of risk)
------------------------------------------------------------------------------*/

clear
use pseudo_data.dta

*keep sample of firms that obtain at least one earmarked loan 
keep if ever_earmark == 1

*drop baseline risk and rename alternative measure of risk
drop risk
rename riskB risk

* Double and triple interactions
gen earmark_risk     = earmark * risk 
gen earmark_MP      = earmark * MP
gen earmark_risk_MP = earmark * risk * MP 
gen risk_MP         = risk * MP

*Variable to inlude Location*Sector*Month fixed effects
egen group_ilm = group(zipcode sector month)

local out      "se bdec(3) rdec(3) symbol(***,**,*)"
local control = "lerner MP BF_rel lvolume collateral maturity"
local FE      = "date rating loan_index firm_id#bank_id bank_id#date" 

* 1) Baseline reg with Location*Sector*Month FE
	reghdfe spread earmark `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table3.xls", replace `out'  ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 2) Baseline with Firm*Year FE
	reghdfe spread earmark `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark) /// 
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  

* 3) Reg by risk with Location*Sector*Month FE
	reghdfe spread earmark earmark_risk risk `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 4) Reg by risk with Firm*Year FE
	reghdfe spread earmark earmark_risk risk `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  
	
* 5) Reg by risk & HHI with Location*Sector*Month FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	sortvar(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 6) Reg by risk & HH with Firm*Year FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon ///
	keep(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	sortvar(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  


insheet using "$results\table3.txt", noname clear
insobs 1, before(1)
replace v1 = "Table 3. Effect of earmarked loans on interest rate spreads of working capital loans (alternative definition of risk)" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("3") sheetreplace
erase "$results\table3.txt"
erase "$results\table3.xls"	

/*------------------------------------------------------------------------------
 TABLE 4: Effect of earmarked loans on interest rate spreads of 
 working capital loans (alternative measure of market power) 
------------------------------------------------------------------------------*/

clear
use pseudo_data.dta

*keep sample of firms that obtain at least one earmarked loan 
keep if ever_earmark == 1

*drop baseline MP and rename alternative measure of market power 
drop MP
rename single_BR MP

* Double and triple interactions
gen earmark_risk     = earmark * risk 
gen earmark_MP      = earmark * MP
gen earmark_risk_MP = earmark * risk * MP 
gen risk_MP         = risk * MP

*Variable to inlude Location*Sector*Month fixed effects
egen group_ilm = group(zipcode sector month)

local out      "se bdec(3) rdec(3) symbol(***,**,*)"
local control = "lerner MP BF_rel lvolume collateral maturity"
local FE      = "date rating loan_index firm_id#bank_id bank_id#date" 
	
* 1) Reg without Time varying demand FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE') cluster(bank_id date)	
	outreg2 using "$results\table4.xls", replace `out' ///
	nocon keep(earmark earmark_risk earmark_risk_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, No)  

* 2) Reg with Location*Sector*Month FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE' group_ilm) cluster(bank_id date)	
	outreg2 using "$results\table4.xls", append `out' ///
	nocon keep(earmark earmark_risk earmark_risk_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  

* 3) Reg with Firm*Year FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE' firm_id#year) cluster(bank_id date)	
	outreg2 using "$results\table4.xls", append `out' ///
	nocon keep(earmark earmark_risk earmark_risk_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  


insheet using "$results\table4.txt", noname clear
insobs 1, before(1)
replace v1 = "Table 4. Effect of earmarked loans on interest rate spreads of working capital loans (alternative measure of market power)" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("4") sheetreplace
erase "$results\table4.txt"
erase "$results\table4.xls"		

/*------------------------------------------------------------------------------
 TABLE 5: Effect of earmarked loans on log volume of 
 working capital loans 
------------------------------------------------------------------------------*/

clear
use pseudo_data.dta

*keep sample of firms that obtain at least one earmarked loan 
keep if ever_earmark == 1

* Double and triple interactions
gen earmark_risk     = earmark * risk 
gen earmark_MP      = earmark * MP
gen earmark_risk_MP = earmark * risk * MP 
gen risk_MP         = risk * MP

*Variable to inlude Location*Sector*Month fixed effects
egen group_ilm = group(zipcode sector month)

local out      "se bdec(3) rdec(3) symbol(***,**,*)"
local control = "lerner MP BF_rel spread collateral maturity"
local FE      = "date rating loan_index firm_id#bank_id bank_id#date" 

* 1) Baseline reg with Location*Sector*Month FE
	reghdfe lvolume earmark `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum lvolume if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table5.xls", replace `out'  ///
	addstat(Mean loan volume (logs), `mean') nocon keep(earmark) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 2) Baseline with Firm*Year FE
	reghdfe lvolume earmark `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum lvolume if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table5.xls", append `out' ///
	addstat(Mean loan volume (logs), `mean') nocon keep(earmark) /// 
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  

* 3) Reg by risk with Location*Sector*Month FE
	reghdfe lvolume earmark earmark_risk risk `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum lvolume if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table5.xls", append `out' ///
	addstat(Mean loan volume (logs), `mean') nocon keep(earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 4) Reg by risk with Firm*Year FE
	reghdfe lvolume earmark earmark_risk risk `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum lvolume if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table5.xls", append `out' ///
	addstat(Mean loan volume (logs), `mean') nocon keep(earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  
	
* 5) Reg by risk & HHI with Location*Sector*Month FE
	reghdfe lvolume earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE' group_ilm) cluster(bank_id date)
	sum lvolume if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table5.xls", append `out' ///
	addstat(Mean loan volume (logs), `mean') nocon keep(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	sortvar(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 6) Reg by risk & HH with Firm*Year FE
	reghdfe lvolume earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum lvolume if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\table5.xls", append `out' ///
	addstat(Mean loan volume (logs), `mean') nocon keep(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	sortvar(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  


insheet using "$results\table5.txt", noname clear
insobs 1, before(1)
replace v1 = "Table 5. Effect of earmarked loans on log volume of working capital loans " if _n == 1 
export excel using "$results\Tables.xlsx", sheet("5") sheetreplace
erase "$results\table5.txt"
erase "$results\table5.xls"	

/*------------------------------------------------------------------------------
 TABLE 6: Test of pre-trends on interest rate spreads of 
 working capital loans
------------------------------------------------------------------------------*/

clear
use pseudo_data.dta

*keep sample of firms that obtain at least one earmarked loan 
keep if ever_earmark == 1

*Indicator variable = 1 the year prior to the first earmarked loan
gen diff = date - date_earmark
gen earmark_pre = diff >=-366 & diff<=-1
drop diff

* Double and triple interactions
gen earmark_risk     = earmark * risk 
gen earmark_MP      = earmark * MP
gen earmark_risk_MP = earmark * risk * MP 
gen risk_MP         = risk * MP

* Double and triple interactions
gen earmark_pre_risk     = earmark_pre * risk 
gen earmark_pre_MP      = earmark_pre * MP
gen earmark_pre_risk_MP = earmark_pre * risk * MP 

*Variable to inlude Location*Sector*Month fixed effects
egen group_ilm = group(zipcode sector month)

local out      "se bdec(3) rdec(3) symbol(***,**,*)"
local control = "lerner MP BF_rel lvolume collateral maturity"
local FE      = "date rating loan_index firm_id#bank_id bank_id#date" 

*1) Introducing Earmark_pre 
	reghdfe spread earmark_pre earmark `control', absorb(`FE') cluster(bank_id date)
	outreg2 using "$results\table6.xls", replace `out'  ///
	nocon keep(earmark_pre earmark) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No) 
	
*2) Introducing Earmark_pre and Location*Sector*Month FE
	reghdfe spread earmark_pre earmark `control', absorb(`FE' group_ilm) cluster(bank_id date)
	outreg2 using "$results\table6.xls", append `out'  ///
	nocon keep(earmark_pre earmark) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes) 
	
*3) Introducing Earmark_pre, heterogeneity by firm risk
	reghdfe spread earmark_pre earmark_pre_risk earmark earmark_risk risk `control', ///
	absorb(`FE') cluster(bank_id date)
	outreg2 using "$results\table6.xls", append `out'  ///
	nocon keep(earmark_pre earmark_pre_risk earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No) 
	
*4) Introducing Earmark_pre, heterogeneity by firm risk and Location*Sector*Month FE
	reghdfe spread earmark_pre earmark_pre_risk earmark earmark_risk risk `control', ///
	absorb(`FE' group_ilm) cluster(bank_id date)
	outreg2 using "$results\table6.xls", append `out'  ///
	nocon keep(earmark_pre earmark_pre_risk earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes) 

*5) Introducing Earmark_pre, heterogeneity by firm risk and market power
	reghdfe spread earmark_pre earmark_pre_risk earmark_pre_MP earmark_pre_risk_MP ///
	earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', ///
	absorb(`FE') cluster(bank_id date)
	outreg2 using "$results\table6.xls", append `out'  ///
	nocon keep(earmark_pre earmark_pre_risk earmark_pre_MP earmark_pre_risk_MP ///
	earmark earmark_risk earmark_MP earmark_risk_MP risk_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No) 
	
*6) Introducing Earmark_pre, heterogeneity by firm risk and market power, and Location*Sector*Month FE
	reghdfe spread earmark_pre earmark_pre_risk earmark_pre_MP earmark_pre_risk_MP ///
	earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control', ///
	absorb(`FE' group_ilm) cluster(bank_id date)
	outreg2 using "$results\table6.xls", append `out'  ///
	nocon keep(earmark_pre earmark_pre_risk earmark_pre_MP earmark_pre_risk_MP ///
	earmark earmark_risk earmark_MP earmark_risk_MP risk_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes) 

insheet using "$results\table6.txt", noname clear
insobs 1, before(1)
replace v1 = "Table 6. Test of pre-trends on interest rate spreads of working capital loans" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("6") sheetreplace
erase "$results\table6.txt"
erase "$results\table6.xls"	

/*------------------------------------------------------------------------------
 TABLE 7: Effect of earmarked loans on long-term credit volume
------------------------------------------------------------------------------*/

clear
use pseudo_data_fm.dta

*keep sample of firms that obtain at least one earmarked loan 
keep if ever_earmark == 1

*Variable to inlude Location*Sector*Month fixed effects
egen group_ilm = group(zipcode sector month)

	*Interactions with baseline MP definition (MP-Herfindahl Concentration)
	gen earmark_risk    = earmark * risk 
	gen earmark_MP      = earmark * MP
	gen earmark_risk_MP = earmark * risk * MP
	gen risk_MP         = risk * MP

	local out      "se bdec(3) rdec(3) symbol(***,**,*)"
	local control = "lerner MP BF_rel lvolume collateral maturity"
	local FE      = "firm_id date" 

*1) Long-term credit and MP
	reghdfe lvolume earmark MP earmark_MP, absorb(`FE') cluster(firm_id date)
	outreg2 using "$results\table7.xls", replace `out'  ///
	nocon keep(earmark earmark_MP) ///
	addtext(Firm FE, Yes, Loc*Sector*Month FE, Yes) 
		
*2) Long-term credit, MP and firm risk
	reghdfe lvolume earmark MP earmark_MP risk earmark_risk, absorb(`FE') cluster(firm_id date)
	outreg2 using "$results\table7.xls", append `out'  ///
	nocon keep(earmark earmark_MP earmark_risk) ///
	addtext(Firm FE, Yes, Loc*Sector*Month FE, Yes)
	
*3) Long-term credit, MP, firm risk and risk*MP
	reghdfe lvolume earmark MP earmark_MP risk earmark_risk earmark_risk_MP risk_MP, absorb(`FE') cluster(firm_id date)
	outreg2 using "$results\table7.xls", append `out'  ///
	nocon keep(earmark earmark_MP earmark_risk earmark_risk_MP) ///
	addtext(Firm FE, Yes, Loc*Sector*Month FE, Yes)
	
	*Using alternative MP definition (MP-single bk relationship)
	drop MP earmark_MP earmark_risk_MP risk_MP
	gen MP = single_BR
	gen earmark_MP      = earmark * MP
	gen earmark_risk_MP = earmark * risk * MP 
	gen risk_MP         = risk * MP

*4) Long-term credit and MP (alternative MP defn)
	reghdfe lvolume earmark MP earmark_MP, absorb(`FE') cluster(firm_id date)
	outreg2 using "$results\table7.xls", append `out'  ///
	nocon keep(earmark earmark_MP) ///
	addtext(Firm FE, Yes, Loc*Sector*Month FE, Yes) 
		
*5) Long-term credit, MP and firm risk (alternative MP defn)
	reghdfe lvolume earmark MP earmark_MP risk earmark_risk, absorb(`FE') cluster(firm_id date)
	outreg2 using "$results\table7.xls", append `out'  ///
	nocon keep(earmark earmark_MP earmark_risk) ///
	addtext(Firm FE, Yes, Loc*Sector*Month FE, Yes)
		
*6) Long-term credit, MP, firm risk and risk*MP (alternative MP defn)
	reghdfe lvolume earmark MP earmark_MP risk earmark_risk earmark_risk_MP risk_MP, absorb(`FE') cluster(firm_id date)
	outreg2 using "$results\table7.xls", append `out'  ///
	nocon keep(earmark earmark_MP earmark_risk earmark_risk_MP) ///
	sortvar(earmark earmark_risk earmark_MP earmark_risk_MP) ///
	addtext(Firm FE, Yes, Loc*Sector*Month FE, Yes)
	

insheet using "$results\table7.txt", noname clear
insobs 1, before(1)
replace v1 = "Table 7. Effect of earmarked loans on long-term credit volume" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("7") sheetreplace
erase "$results\table7.txt"
erase "$results\table7.xls"		


/*------------------------------------------------------------------------------
 APPENDIX TABLE A2: Interest rate spreads of 
 working capital loans and 2-digit zipcode Herfindahl Index
------------------------------------------------------------------------------*/

clear
use pseudo_data.dta

local out      "se bdec(3) rdec(3) symbol(***,**,*)"
local control = "BF_rel HH lvolume collateral maturity"

*1) All firms 
	reghdfe spread `control', cluster(bank_id date)
	outreg2 using "$results\tableA2.xls", replace `out'  ///
	nocon keep(HH) addtext(Sample, All firms, Municipality FE, No) 
	
*2) All firms, with Municipality FE
	reghdfe spread `control', absorb( mun) cluster(bank_id date)
	outreg2 using "$results\tableA2.xls", append `out'  ///
	nocon keep(HH) addtext(Sample, All firms, Municipality FE, Yes) 

*3) Firms with earmarked loans
	reghdfe spread `control' if ever_earmark == 1, cluster(bank_id date)
	outreg2 using "$results\tableA2.xls", append `out'  ///
	nocon keep(HH) addtext(Sample, Firms with earmarked loans, Municipality FE, No) 
	
*4) Firms with earmarked loans, with Municipality FE
	reghdfe spread `control' if ever_earmark == 1, absorb(mun) cluster(bank_id date)
	outreg2 using "$results\tableA2.xls", append `out'  ///
	nocon keep(HH) addtext(Sample, Firms with earmarked loans, Municipality FE, Yes) 	
		 

insheet using "$results\tableA2.txt", noname clear
insobs 1, before(1)
replace v1 = "Appendix Table A2. Interest rate spreads of working capital loans and 2-digit zipcode Herfindahl Index" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("A2") sheetreplace
erase "$results\tableA2.txt"
erase "$results\tableA2.xls"

/*------------------------------------------------------------------------------
 APPENDIX TABLE A3: Effect of earmarked loans on interest rate spreads of 
 working capital loans (12 months before/after first earmarked loan)
------------------------------------------------------------------------------*/

clear
use pseudo_data.dta

*keep sample of firms that obtain at least one earmarked loan 
keep if ever_earmark == 1

*Days to/from date of first earmarked loan
gen diff = date - date_earmark

* Double and triple interactions
gen earmark_risk     = earmark * risk 
gen earmark_MP      = earmark * MP
gen earmark_risk_MP = earmark * risk * MP 
gen risk_MP         = risk * MP

*Variable to inlude Location*Sector*Month fixed effects
egen group_ilm = group(zipcode sector month)

local out     = "se bdec(3) rdec(3) symbol(***,**,*)"
local control = "lerner MP BF_rel lvolume collateral maturity"
local FE      = "date rating loan_index firm_id#bank_id bank_id#date" 
local IF      = "if diff>=-365 & diff<=365"

* 1) Baseline reg with Location*Sector*Month FE
	reghdfe spread earmark `control' `IF', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\tableA3.xls", replace `out'  ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 2) Baseline with Firm*Year FE
	reghdfe spread earmark `control' `IF', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\tableA3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark) /// 
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  

* 3) Reg by risk with Location*Sector*Month FE
	reghdfe spread earmark earmark_risk risk `control' `IF', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\tableA3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 4) Reg by risk with Firm*Year FE
	reghdfe spread earmark earmark_risk risk `control' `IF', absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\tableA3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  
	
* 5) Reg by risk & HHI with Location*Sector*Month FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP ///
	`control' `IF', absorb(`FE' group_ilm) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\tableA3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	sortvar(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, Yes, Firm*Year FE, No)  
	
 * 6) Reg by risk & HH with Firm*Year FE
	reghdfe spread earmark earmark_risk risk MP earmark_MP earmark_risk_MP risk_MP `control' `IF', ///
	absorb(`FE' firm_id#year) cluster(bank_id date)
	sum spread if e(sample)
	local mean = r(mean)	
	outreg2 using "$results\tableA3.xls", append `out' ///
	addstat(Mean interest rate spread, `mean') nocon keep(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	sortvar(earmark earmark_risk earmark_risk_MP risk_MP earmark_MP) ///
	addtext(Firm*Bank FE, Yes, Bank*Month FE, Yes, Loc*Sector*Month FE, No, Firm*Year FE, Yes)  


insheet using "$results\tableA3.txt", noname clear
insobs 1, before(1)
replace v1 = "Appendix Table A3. Effect of earmarked loans on interest rate spreads of working capital loans (12 months before/after first earmarked loan)" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("A3") sheetreplace
erase "$results\tableA3.txt"
erase "$results\tableA3.xls"	

/*------------------------------------------------------------------------------
 APPENDIX TABLE A4: Probability of Bundling Earmarked Loans and 
 Working Capital Loans
------------------------------------------------------------------------------*/

clear
use pseudo_data_fb.dta

* Double interaction
gen risk_MP = risk * MP

local out     = "se bdec(3) rdec(3) symbol(***,**,*)"
local note    = ""

*1) Probabilty of bundling and MP
	reghdfe bundle age size BF_rel MP, absorb(bank_id) cluster(bank_id)
	outreg2 using "$results\tableA4.xls", replace `out'	nocon addtext(Bank FE, Yes)  

*2) Probabilty of bundling, MP and Risk
	reghdfe bundle age size BF_rel MP risk, absorb(bank_id) cluster(bank_id)
	outreg2 using "$results\tableA4.xls", append `out' nocon addtext(Bank FE, Yes)  

*3) Probabilty of bundling, MP, Risk and Risk_MP
	reghdfe bundle age size BF_rel MP risk risk_MP, absorb(bank_id) cluster(bank_id)
	outreg2 using "$results\tableA4.xls", append `out' nocon addtext(Bank FE, Yes)  
	
** Using alternative definition of risk 
	drop risk risk_MP
	rename riskB risk
	gen risk_MP = risk * MP
	
*4) Probabilty of bundling and alternative def of Risk
	reghdfe bundle age size BF_rel risk, absorb(bank_id) cluster(bank_id)
	outreg2 using "$results\tableA4.xls", append `out' nocon addtext(Bank FE, Yes)  
	
*5) Probabilty of bundling, MP and alternative def of Risk
	reghdfe bundle age size BF_rel MP risk, absorb(bank_id) cluster(bank_id)
	outreg2 using "$results\tableA4.xls", append `out' nocon addtext(Bank FE, Yes)  
	
*6) Probabilty of bundling, MP, alternative def of Risk with MP interaction
	reghdfe bundle age size BF_rel MP risk risk_MP, absorb(bank_id) cluster(bank_id)
	outreg2 using "$results\tableA4.xls", append `out' nocon addtext(Bank FE, Yes)  
	

insheet using "$results\tableA4.txt", noname clear
insobs 1, before(1)
replace v1 = "Appendix Table A4. Probability of Bundling Earmarked Loans and Working Capital Loans" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("A4") sheetreplace
erase "$results\tableA4.txt"
erase "$results\tableA4.xls"	
	
/*------------------------------------------------------------------------------
 APPENDIX TABLE B1: Firm-Bank Pair Probability of Starting Earmarked Credit 
------------------------------------------------------------------------------*/

clear
use pseudo_data_fby.dta

bysort firm_id: egen ever_ear = max(earmark)
gen pre_ear = earmark==0 
sort firm_id bank_id year
bysort firm_id bank_id : replace pre_ear = 1 if earmark==1 & earmark[_n-1]==0 
bysort firm_id bank_id : replace pre_ear = 1 if earmark[1]==1

*for each firm-bank pair, keep periods upto the first earmarked loan 
keep if pre_ear == 1

*Create interactions and FE variables
egen group_sy  = group(sector year)
gen Lrgbk_BF   = Lrgbk * BF_rel
gen Lrgbk_size = Lrgbk * size
gen Lrgbk_risk = Lrgbk * risk
gen Leqbk_risk = Leqbk * risk
gen MP_risk   = MP * risk

local controls  "BF_rel MP size age risk Lrgbk Leqbk"
local controlsB "`controls' Lrgbk_BF Lrgbk_size Lrgbk_risk Leqbk_risk MP_risk"

*Probability of earmarked credit- baseline
	reghdfe earmark `controls', abs(group_sy) cluster(bank_id)
	outreg2 using "$results\tableB1.xls", replace `out' keep(`controls') addtext(Sector*Year FE, Yes)  
	
*Col 2
	reghdfe earmark `controlsB', abs(group_sy) cluster(bank_id)
	outreg2 using "$results\tableB1.xls", append `out' keep(`controlsB') addtext(Sector*Year FE, Yes)  
	
*Col 3
	reghdfe earmark `controls' if ever_ear == 1, abs(group_sy) cluster(bank_id)
	outreg2 using "$results\tableB1.xls", append `out' keep(`controls') addtext(Sector*Year FE, Yes)  

*Col 4
	reghdfe earmark `controlsB' if ever_ear == 1, abs(group_sy) cluster(bank_id)
	outreg2 using "$results\tableB1.xls", append `out' keep(`controlsB') addtext(Sector*Year FE, Yes)  

insheet using "$results\tableB1.txt", noname clear
insobs 1, before(1)
replace v1 = "Appendix Table B1. Firm-Bank Pair Probability of Starting Earmarked Credit" if _n == 1 
export excel using "$results\Tables.xlsx", sheet("B1") sheetreplace
erase "$results\tableB1.txt"
erase "$results\tableB1.xls"

/*------------------------------------------------------------------------------
 FIGURE 2: Monetary policy rate, inflation rate, and 
 interest rates of earmarked loans
------------------------------------------------------------------------------*/


/*------------------------------------------------------------------------------
 FIGURE 3: Quarterly evolution of working capital loan terms 12 months 
 to/from time of first earmarked loan
------------------------------------------------------------------------------*/

clear
use pseudo_data.dta

*keep sample of firms that obtain at least one earmarked loan 
keep if ever_earmark == 1

*Days from/to the first earmarked loan
gen diff = date - date_earmark

*quarterly-dummies 	
gen ear_q0 = diff >=0 & diff<=90
gen ear_q1 = diff >=91 & diff<=180
gen ear_q2 = diff >=181 & diff<=270
gen ear_q3 = diff >=271 & diff<=365
gen ear_q1bef = diff >=-90 & diff<=-1
gen ear_q2bef = diff >=-180 & diff<=-91

*Panel A. Interest rate spreads

	local controls = "BF_rel MP lvolume collateral maturity"
	local FE       = "date rating loan_index firm_id#bank_id bank_id#date" 

	reghdfe spread ear_q2bef ear_q1bef ear_q0 ear_q1 ///
	ear_q2 ear_q3 `controls' if diff>=-365 & diff<=365, ///
	absorb($FE) cluster(bank_id date)
	
	coefplot, keep(ear_q2bef ear_q1bef ear_q0 ear_q1 ear_q2 ear_q3) vertical ///
	yline(0) ciopts(recast(rcap)) coeflabels(ear_q2bef = "-2q" ///
	ear_q1bef = "-1q" ear_q0= "0q" ear_q1= "1q" ear_q2= "2q" ear_q3= "3q")

	graph save "$results\Fig3A.gph", replace

*Panel B. Loan volume (logs)

	local controls = "BF_rel MP spread collateral maturity"
	local FE       = "date rating loan_index firm_id#bank_id bank_id#date" 

	reghdfe lvolume ear_q2bef ear_q1bef ear_q0 ear_q1 ///
	ear_q2 ear_q3 `controls' if diff>=-365 & diff<=365, ///
	absorb($FE) cluster(bank_id date)
	
	coefplot, keep(ear_q2bef ear_q1bef ear_q0 ear_q1 ear_q2 ear_q3) vertical ///
	yline(0) ciopts(recast(rcap)) coeflabels(ear_q2bef = "-2q" ///
	ear_q1bef = "-1q" ear_q0= "0q" ear_q1= "1q" ear_q2= "2q" ear_q3= "3q")

	graph save "$results\Fig3B.gph", replace
